﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.IO;
using System.Windows.Forms;

namespace MonitorFileConvert
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            StringBuilder sb=new StringBuilder ();
            sb.Append(" select ' ");
            sb.Append("( '+convert(varchar(10),a.F1)+', ''1'',''1'','+convert(varchar(10), b.F1 )+','''','''','''+isnull(convert(varchar(200),F3),'')+''','''+isnull(convert(varchar(200),F3),'')+''','''");
            sb.Append(" +isnull(convert(varchar(200),a.F2),'')+''',''0'',''0'','''+isnull(convert(varchar(100),a.F12),'')+''','''','''+isnull(convert(varchar(10),d.[id]),'1')+''','''','''");
            sb.Append(" +isnull(convert(varchar(500),a.F10),'')+''','''+convert(varchar(10), dateadd(year,F5*(-1),getdate()),111)+''','''+isnull(convert(varchar(50),F9),'')+''','''','''");
             sb.Append("+isnull(convert(varchar(30),a.F6),'')+''','''','''+isnull(convert(varchar(1000),F11),'')+''','''','''')'");

             sb.Append(" from TeacherInfo$ as a join 学位 as b on a.F8=b.F2  ");
             sb.Append("  left join  性别 as d on a.F4=d.sex");

             DataTable dt = DbHelperSQL.Query(sb.ToString()).Tables[0];
             FileStream fs = new FileStream(@"d:\sql.txt", FileMode.Create, FileAccess.Write);
             StreamWriter sw=new StreamWriter(fs);
            sw.BaseStream.Seek(0,SeekOrigin.Begin);
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 label1.Text = "TeacherInfo：" + i.ToString();
                 string xstr="INSERT INTO dbo.Teacher_Info( UserID ,CountyID ,LanguageID ,EducationID ,State ,City ,FamilyName ,FirstName ,ChineseName ,AuditStatus ,AdminID ,SpecialType ,";
                 xstr+=" SpecialLevel ,Sex ,IdentityNum ,GraduateFrom ,Birthday,Major,NowAddress,MobliePhone,TelPhone,Introduce ,TeachType ,TeachLevel)VALUES ";
                 sw.WriteLine(xstr+dt.Rows[i][0]);
             }
             StringBuilder strBasic = new StringBuilder();
              strBasic.Append( " select '('+convert(varchar(10),F1)+','''+convert(varchar(200),F3)+''','''+convert(varchar(200),F7)+''','''',");
              strBasic.Append(" ''12'',''1'','''+convert(varchar(20),getdate(),120)+''',''127.0.0.1'',''1'','''+'ActivateInfo'+''',''/images/no-Photo.gif'')'");
              strBasic.Append("  from TeacherInfo$ ");
            sw.WriteLine();
            sw.WriteLine("-----------------------UserBasicInfo-------------------------------------------");
            sw.WriteLine();
            DataTable dt1=DbHelperSQL.Query(strBasic.ToString()).Tables[0];
            for(int j=0;j<dt1.Rows.Count;j++)
            {
                label1.Text="BasicInfo:"+j.ToString();
                string ystr="INSERT INTO [User_BasicInfo] ([UserID] ,[UserName] ,[Email] ,[UserPass] ,[Level] ,[UserStatus] ,";
                ystr+=" [RegistTime] ,[RegistIP] ,[UserType] ,[ActivateInfo]  ,[HeadPic]  ) VALUES ";
                sw.WriteLine(ystr+dt1.Rows[j][0].ToString());
            }
            sw.WriteLine();
            sw.WriteLine("-----------------------ResourceInfo-------------------------------------------");
            sw.WriteLine();
            StringBuilder strSource = new StringBuilder();
            strSource.Append(" select '0,'''+convert(varchar(100),F5)+''','''+convert(varchar(10),F9)+''','''");
            strSource.Append(" +convert(varchar(10),F4)+''','''+convert(varchar(500),F3)+''',0,'''+convert(varchar(200),F8)+''','''");
            strSource.Append(" +convert(varchar(2000),F11)+''',''127.0.0.1'','''+convert(varchar(10),F10)+''','''");
            strSource.Append(" +convert(varchar(2000),F12)+''',''2'',1'");
            strSource.Append(" from CoursewareInfo$ ");
            DataTable dt2 = DbHelperSQL.Query(strSource.ToString()).Tables[0];
            for (int g = 0; g < dt2.Rows.Count; g++)
            {
                label1.Text = "Resource:" + g.ToString();
                string zstr = "EXEC UP_Resource_Info_AddOrUpdate ";
                sw.WriteLine(zstr+dt2.Rows[g][0].ToString());
            }
             label1.Text = "完毕";
            sw.Close();

        }
        #region 备用
        //        update CoursewareInfo$ set F5=TypeID from CoursewareInfo$,[CourseType]
        //where CoursewareInfo$.F5=[CourseType].[TypeName]

        //update CoursewareInfo$ set F6=TypeID from CoursewareInfo$,[CourseType]
        //where CoursewareInfo$.F6=[CourseType].[TypeName]

        //update CoursewareInfo$ set F7=TypeID from CoursewareInfo$,[CourseType]
        //where CoursewareInfo$.F7=[CourseType].[TypeName]

        //update  CoursewareInfo$ set F5=F5+','+F6+','+F7+','

        //update CoursewareInfo$ set F8=replace(F8,'/',',')


        //update CoursewareInfo$ set F10=ShowID from CoursewareInfo$,[Show]
        //where CoursewareInfo$.F10=[Show].[ShowType]

        //update CoursewareInfo$ set F9=F4 where F1<100
        //update CoursewareInfo$ set F9=4 where F1>=100 and F1<120

        //update CoursewareInfo$ set F9=5 where F1>=120 and F1<128
        //update CoursewareInfo$ set F9=6 where F1>=128 and F1<138
        //update CoursewareInfo$ set F9=7 where F1>=138 and F1<159
        //update CoursewareInfo$ set F9=8 where F1>=159 and F1<176
        //update CoursewareInfo$ set F9=9 where F1>=176 and F1<200
        //update CoursewareInfo$ set F9=10 where F1>=200 and F1<246
        #endregion
    }
}

